package me.xuym.crawler.house58statistics;

import com.cbs.java.component.application.Application;
import com.cbs.java.component.database.Database;
import com.cbs.java.component.database.mysql.MySqlDatabase;
import me.xuym.crawler.Loader;
import me.xuym.crawler.simpleextension.entity.SimpleEmail;

import java.util.List;

/**
 * Created by broche on 10/09/2017.
 */
public class House58StatisticsLoader extends Loader<SimpleEmail> {
    private Database database;

    public House58StatisticsLoader(Application application) throws Exception {
        super(application);

        database = new MySqlDatabase("home.xuym.me", "3306", "root", "Tomato.14210", "crawler");
    }

    @Override
    protected SimpleEmail onLoad() throws Exception {
        long countaddress = database.scalar("select count(distinct addressfinal) from house58address", Long.class);
        long countinfo = database.scalar("select count(1) from house58", Long.class);
        List<House58Statistics> results = database.query("select b.addressfinal, min(b.py) as py, max(b.sort) as sort, max(a.unitprice) as maxprice, min(a.unitprice) as minprice, avg(a.unitprice) as avgprice, max(a.size) as maxsize, min(a.size) as minsize, count(1) as count from house58 a, house58address b where a.address=b.address group by b.addressfinal order by sort desc, py", House58Statistics.class);

        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("<html><body>");
        stringBuilder.append("小区数量: " + countaddress + "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;房屋数量: " + countinfo);
        stringBuilder.append("<br/><br/>");
        stringBuilder.append("<table width='100%'");
        stringBuilder.append("<tr style=\"background-color: lightgray;\"><th>小区</th><th>最高价</th><th>最低价</th><th>均价</th><th>最大面积</th><th>最小面积</th><th>数量</th></tr>");
        int index = 0;
        for (House58Statistics result : results) {
            index++;
            stringBuilder.append(String.format("<tr style=\"background-color: %s;\"><td>%s</td><td>%.2f</td><td>%.2f</td><td>%.2f</td><td>%.2f</td><td>%.2f</td><td>%d</td></tr>", index % 2 == 0 ? "ghostwhite" : "white", result.getAddressfinal(), result.getMaxprice(), result.getMinprice(), result.getAvgprice(), result.getMaxsize(), result.getMinsize(), result.getCount()));
        }
        stringBuilder.append("</table>");
        stringBuilder.append("</body></html>");

        SimpleEmail simpleEmail = new SimpleEmail();
        simpleEmail.setContent(stringBuilder.toString());
        simpleEmail.setSubject("【58同城统计】");
        return simpleEmail;
    }
}
